# http://www.pythondoc.com/flask-sqlalchemy/
# Valid SQLite URL forms are:
#  sqlite:///:memory: (or, sqlite://)
#  sqlite:///relative/path/to/file.db
#  sqlite:////absolute/path/to/file.db

# pip install Flask Flask-SQLAlchemy


from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy

import time
import datetime

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///users.db'
app.config['SQLALCHEMY_ECHO'] = True  #查询时会显示原始SQL语句 
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # 关闭数据库修改跟踪操作[提高性能]
db = SQLAlchemy(app)



class User(db.Model):
    # 定义表名
    __tablename__ = 'data'
    id = db.Column(db.Integer, primary_key=True)
    option = db.Column(db.String(80), nullable=False)
    timestamp = db.Column(db.String(20), nullable=False)
    def __repr__(self):
        return "(%s, %s, %s)"%(self.id, self.option, self.timestamp)
    
with app.app_context():
    # 初始化数据库，要放在数据库模型之后
    #db.init_app(app)
    #db.drop_all() #删除所有的表
    db.create_all() #创建所有的表




@app.route('/')
def index():
    return render_template('index.html')

@app.route('/submit', methods=['POST'])
def submit():
    option = request.form['option']
    user = User(option=option, timestamp=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    db.session.add(user)
    db.session.commit()
    return redirect('https://www.baidu.com')


@app.route('/stats')
def stats():
    options = User.query.with_entities(User.option, db.func.count(User.option)).group_by(User.option).all()
    return render_template('stats.html', options=options)


if __name__ == '__main__':  
    #db.create_all()
    app.run(host="0.0.0.0", port=5000, debug=True)